const express = require('express');
const saleRouter = express.Router();
const { sendResult } = require('../utils/index')

const mysql = require('mysql')
const db = mysql.createPool({
    host: "127.0.0.1",
    user: "root",
    password: "admin123",
    database: "mydatabase_01",
    port: 3306,
})


const table = {
    name: 'sale',
}

saleRouter.use(express.json());
saleRouter.use(express.urlencoded({ extended: true }));

//查找
// saleRouter.get("/list", (req, res) => {
//     db.query(`SELECT * FROM ${table.name}`, (err, result) => {
//         if (err) {
//             console.error(err)
//             res.status(500).send("Error retrieving data from database")
//         }

//         if (req && req.query) {
//             const { pageSize, pages } = req.query
//             const startIndex = (pages - 1) * pageSize
//             if (pageSize && pages) {
//                 if (pageSize >= result.length) {
//                     res.send({
//                         total: result.length,
//                         data: result
//                     })
//                 } else {
//                     const allPage = Math.ceil(result.length / pageSize)
//                     if (pages >= allPage) {
//                         res.send({
//                             total: result.length,
//                             data: result.slice(result.length - pageSize)
//                         })
//                     } else {
//                         res.send({
//                             total: result.length,
//                             data: result.slice(startIndex, startIndex + pageSize)
//                         })
//                     }
//                 }
//             } else {
//                 res.send(result)
//             }
//         } else {
//             res.send(result)
//         }
//     })
// })

saleRouter.get('/sale/list', (req, res) => {
    const { pageSize, pages } = req.query
    const startIndex = (pages - 1) * pageSize
    console.log(pageSize, pages, startIndex, 'list');
    let sql = `SELECT * FROM ${table.name} limit ${pageSize} offset ${startIndex}`

    if (pages && pageSize) {

        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return

            } else {
                sendResult(res, result)
                return
            }
        })
    } else {
        sql = `SELECT * FROM ${table.name}`
        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return
            }
            else {
                sendResult(res, result)
                return
            }

        })
    }


})

//新增 修改
saleRouter.post('/sale/edit', (req, res) => {
    let { id } = req.query
    let { countyName, totalSales, countySaleFirst, firstSales, OnlineStore, offlineStore, notes } = req.body
    if (id) {
        if (countyName && totalSales) {
            db.query(`UPDATE ${table.name} SET countyName = '${countyName}',countySaleFirst = '${countySaleFirst}',firstSales = '${firstSales}', OnlineStore = '${OnlineStore}', offlineStore = '${offlineStore}', notes = '${notes}', totalSales = '${totalSales}' WHERE id = ${id}`, (err, result) => {
                if (err) {
                    res.status(500).send(err.response)
                } else {
                    sendResult(res, {
                        msg: '修改成功'
                    })
                }
            }
            )
        } else {
            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)
        }
    } else {
        if (!countyName || !totalSales) {
            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)
        } else {
            db.query(`INSERT INTO ${table.name} (countyName, totalSales , countySaleFirst, firstSales, OnlineStore, offlineStore, notes) VALUES ('${countyName}', '${totalSales}', '${countySaleFirst}', '${firstSales}', '${OnlineStore}', '${offlineStore}', '${notes}')`, (err, result) => {
                if (err) {
                    console.log(err.errno);
                    if (err.errno === 1062) {
                        sendResult(res, {
                            msg: '该数据已存在'
                        }, 201)
                    } else {
                        res.status(500).send(err)
                    }

                } else {
                    sendResult(res, {
                        msg: '新增成功！'
                    })
                }
            })
        }

    }

})

//删除
saleRouter.get('/sale/del', (req, res) => {
    let { id } = req.query
    db.query(`DELETE FROM ${table.name} WHERE id = ${id}`, (err, result) => {
        if (err) {
            res.status(500).send(err)
        } else {
            sendResult(res, {
                msg: '删除成功'
            })
        }
    })
})

module.exports = saleRouter